package com.usian.service.impl;

import com.usian.mapper.CourseMapper;
import com.usian.mapper.StudentMapper;
import com.usian.pojo.Course;
import com.usian.pojo.Student;
import com.usian.service.StudentService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Autowired
    private CourseMapper courseMapper;

    @Override
    public List<Student> findAll() {
        return studentMapper.findAll();
    }

    @Override
    public void addToDatabase(MultipartFile file) throws Exception{
        List<Student> studentList = new ArrayList<>();                    // 创建StudentList
        List<Course> courseList = new ArrayList<>();                    // 创建CourseList

        String fileName = file.getOriginalFilename();                           // 获取上传的文件名
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);    // 获取上传文件后缀

        System.out.println("文件后缀为：" + suffix);
        InputStream inputStream = file.getInputStream();            // 输入流读取文件
        Workbook workbook = null;                                   // 1. 新建工作簿

        if(suffix.equals("xlsx")){
            workbook = new XSSFWorkbook(inputStream);               // Excel2007版本及以后 用XSSF(即Poi-ooxml)处理
        } else {
            workbook = new HSSFWorkbook(inputStream);               // Excel2003版本 用HSSF(即Poi)处理
        }

        //第1页
        Sheet sheet1 = workbook.getSheetAt(0);                     // 2. 获取当前工作表

        if(sheet1 != null) {
            for (int i = 1; i <= sheet1.getLastRowNum(); i++) {       // 3. 从第二行开始遍历工作表的每一行
                Row row = sheet1.getRow(i);
                if (row != null) {
                    List<String> list = new ArrayList<>();
                    for (Cell cell : row) {                          // 4. 遍历每一个单元格
                        if (cell != null) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);        // 将单元格值转化为字符串
                            String cellValue = cell.getStringCellValue();   // 获取字符串形式的值
                            list.add(cellValue);                            // 将值加入组
                        }
                    }

                    // 值的转化
                    String name = list.get(0);
                    Integer age = Integer.parseInt(list.get(1));
                    String sex = list.get(2);

                    // 构造一个账单对象，并将从个单元格获取的值赋给它
                    Student Student = new Student(name, age, sex);
                    studentList.add(Student);                                 // 将新的一条账单加入StudentList
                }
            }

            //第2页
            Sheet sheet2 = workbook.getSheetAt(1);                     // 2. 获取当前工作表

            if (sheet2 != null) {
                for (int i = 1; i <= sheet2.getLastRowNum(); i++) {       // 3. 从第二行开始遍历工作表的每一行
                    Row row = sheet2.getRow(i);
                    if (row != null) {
                        List<String> list = new ArrayList<>();
                        for (Cell cell : row) {                          // 4. 遍历每一个单元格
                            if (cell != null) {
                                cell.setCellType(Cell.CELL_TYPE_STRING);        // 将单元格值转化为字符串
                                String cellValue = cell.getStringCellValue();   // 获取字符串形式的值
                                list.add(cellValue);                            // 将值加入组
                            }
                        }

                        // 值的转化
                        String name = list.get(0);

                        // 构造一个账单对象，并将从个单元格获取的值赋给它
                        Course course = new Course(name);
                        courseList.add(course);                                 // 将新的一条账单加入StudentList
                    }
                }



            /*for (Student Student: StudentList){
               studentMapper.addStudentExcelFileToDatabase(Student);      // 将每一条账单插入数据库
            }*/
                studentMapper.addStudentExcelFileToDatabase(studentList);      // 批量导入
                courseMapper.addStudentExcelFileToDatabase(courseList);      // 批量导入
            }
        }
    }
}
